package com.ops.service.ns.system.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;
import oracle.jdbc.OracleTypes;
import com.ops.service.ns.system.idao.IOnlinePayrollSystemDAO;
import com.ops.service.ns.system.exceptionhandler.ExceptionHandler;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import com.ops.service.ns.system.exception.OPSException;
import com.ops.service.ns.system.constant.OPSConstant;
import com.ops.service.ns.system.helper.OPSHelper;
import com.ops.schema.ns.system.OPSUserAction;
import com.ops.schema.ns.system.OPSEmployer;
import java.lang.Long;


public class OnlinePayrollSystemDAO implements IOnlinePayrollSystemDAO {
	private static final Logger LOG_DAO = Logger.getLogger(OnlinePayrollSystemDAO.class.getName());
	private JdbcTemplate jdbc;	
    public void setJdbc(JdbcTemplate jdbc)
    {
    	this.jdbc=jdbc;
    }
    
	@Override
	public Map login(final String userId,final String password,final String userSession)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside login:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}

		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_password", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_session_id", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("p_user", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("userId", rs.getString("USER_ID"));
						     hm.put("userName", rs.getString("USER_NAME"));
						     hm.put("userStatus", rs.getString("USER_STATUS"));
						     hm.put("password", rs.getString("USER_PASSWORD"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("p_roles", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("roleId", rs.getString("ROLE_ID"));
						     hm.put("roleName", rs.getString("ROLE_NAME"));
						     hm.put("roleStatus", rs.getString("ROLE_STATUS"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("p_menu", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("menuId", rs.getString("menuId"));
						     hm.put("menuName", rs.getString("menuName"));
						     hm.put("menuStatus", rs.getString("menuStatus"));
						     hm.put("parentMenuId", rs.getString("parentMenuId"));
						     hm.put("menuClass", rs.getString("menuClass"));
						     hm.put("menuDivision", rs.getString("menuDivision"));
						     hm.put("menuURL", rs.getString("menuURL"));
						     hm.put("createdBy", rs.getString("createdBy"));
						     hm.put("createdDate", rs.getString("createdDate"));
						     hm.put("createdSession", rs.getString("createdSession"));
						     hm.put("updatedBy", rs.getString("updatedBy"));
						     hm.put("updatedDate", rs.getString("updatedDate"));
						     hm.put("updatedSession", rs.getString("updatedSession"));
						     hm.put("deletedBy", rs.getString("deletedBy"));
						     hm.put("deletedDate", rs.getString("deletedDate"));
						     hm.put("deletedSession", rs.getString("deletedSession"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_LOGIN(?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++,userId);
				cstmt.setString(fieldCounter++,password);				
				cstmt.setString(fieldCounter++,userSession);
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit login:DAO");
		return mpResult;

	}  
	
	@Override
	public Map logout(final String userId, final String userSession) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside logout:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}

		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_session_id", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_LOGOUT(?,?,?,?)");
				cstmt.setString(fieldCounter++,userId);
				cstmt.setString(fieldCounter++,userSession);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit logout:DAO");
		return mpResult;

	} 	
	
	@Override
	public Map retrieveMenu(final String userId,final String roleId)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside retrieveMenu:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}

		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_role_id", Types.VARCHAR));		
		lstParams.add(new SqlOutParameter("p_menus", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("menuId", rs.getString("menuId"));
						     hm.put("menuName", rs.getString("menuName"));
						     hm.put("menuStatus", rs.getString("menuStatus"));
						     hm.put("parentMenuId", rs.getString("parentMenuId"));
						     hm.put("parentMenuId", rs.getString("menuClass"));
						     hm.put("menuDivision", rs.getString("menuDivision"));
						     hm.put("menuURL", rs.getString("menuURL"));
						     hm.put("createdBy", rs.getString("createdBy"));
						     hm.put("createdDate", rs.getString("createdDate"));
						     hm.put("createdSession", rs.getString("createdSession"));
						     hm.put("updatedBy", rs.getString("updatedBy"));
						     hm.put("updatedDate", rs.getString("updatedDate"));
						     hm.put("updatedSession", rs.getString("updatedSession"));
						     hm.put("deletedBy", rs.getString("deletedBy"));
						     hm.put("deletedDate", rs.getString("deletedDate"));
						     hm.put("deletedSession", rs.getString("deletedSession"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_RETRIEVE_MENUS(?,?,?,?,?)");
				cstmt.setString(fieldCounter++, userId);
				cstmt.setString(fieldCounter++, roleId);				
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_RETR_MENUS());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_RETR_MENUS());
		}
		LOG_DAO.info("Exit retrieveMenu:DAO");
		return mpResult;

	}	
	
	@Override
	public Map recordAction(final String action, final String objectType, final String objectId, final String userSession, final String userId)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside recordAction:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}

		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_user_action", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_object_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_object_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_session", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_RECORD_USER_ACTION(?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, action);
				cstmt.setString(fieldCounter++, objectType);
				cstmt.setString(fieldCounter++, objectId);
				cstmt.setString(fieldCounter++, userId);
				cstmt.setString(fieldCounter++, userSession);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_RECORD_ACTION());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_RECORD_ACTION());
		}
		LOG_DAO.info("Exit recordAction:DAO");
		return mpResult;

	}
	
	@Override
	public Map updateEmployer(final OPSEmployer employer)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside updateEmployer:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}

		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("employerId", Types.VARCHAR));
		lstParams.add(new SqlParameter("employerName", Types.VARCHAR));
		lstParams.add(new SqlParameter("employerDBA", Types.VARCHAR));
		lstParams.add(new SqlParameter("address1", Types.VARCHAR));
		lstParams.add(new SqlParameter("address2", Types.VARCHAR));
		lstParams.add(new SqlParameter("city", Types.VARCHAR));
		lstParams.add(new SqlParameter("state", Types.VARCHAR));
		lstParams.add(new SqlParameter("country", Types.VARCHAR));
		lstParams.add(new SqlParameter("zipCode", Types.VARCHAR));
		lstParams.add(new SqlParameter("phone", Types.VARCHAR));
		lstParams.add(new SqlParameter("fax", Types.VARCHAR));
		lstParams.add(new SqlParameter("website", Types.VARCHAR));
		lstParams.add(new SqlParameter("taxId", Types.VARCHAR));
		lstParams.add(new SqlParameter("entityType", Types.VARCHAR));
		lstParams.add(new SqlParameter("establishmentDate", Types.DATE));
		lstParams.add(new SqlParameter("sicCode", Types.VARCHAR));
		lstParams.add(new SqlParameter("createdBy", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("employer", OracleTypes.CURSOR,
			new RowMapper<Map<String, String>>()
			{
				public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
				   {
					     Map<String, String> hm = new HashMap<String, String>();
					     hm.put("employerId", rs.getString("employerId"));
					     hm.put("employerName", rs.getString("employerName"));
					     hm.put("employerDBA", rs.getString("employerDBA"));
					     hm.put("address1", rs.getString("address1"));
					     hm.put("address2", rs.getString("address2"));
					     hm.put("city", rs.getString("city"));
					     hm.put("state", rs.getString("state"));
					     hm.put("country", rs.getString("country"));
					     hm.put("zipCode", rs.getString("zipCode"));
					     hm.put("phone", rs.getString("phone"));
					     hm.put("fax", rs.getString("fax"));
					     hm.put("website", rs.getString("website"));
					     hm.put("taxId", rs.getString("taxId"));
					     hm.put("entityType", rs.getString("entityType"));
					     hm.put("establishmentDate", rs.getString("establishmentDate"));
					     hm.put("sicCode", rs.getString("sicCode"));
					     hm.put("createdBy", rs.getString("createdBy"));
					     hm.put("createdDate", rs.getString("createdDate"));
					     hm.put("createdSession", rs.getString("createdSession"));
					     hm.put("updatedBy", rs.getString("updatedBy"));
					     hm.put("updatedDate", rs.getString("updatedDate"));
					     hm.put("updatedSession", rs.getString("updatedSession"));
					     hm.put("deletedBy", rs.getString("deletedBy"));
					     hm.put("deletedDate", rs.getString("deletedDate"));
					     hm.put("deletedSession", rs.getString("deletedSession"));
					     return hm;
				    }
			}
		));		
		lstParams.add(new SqlOutParameter("statusCode", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("statusMessage", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL CREATE_UPDATE_EMPLOYER(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++,employer.getId());
				cstmt.setString(fieldCounter++,employer.getName());
				cstmt.setString(fieldCounter++,employer.getDba());
				cstmt.setString(fieldCounter++,employer.getAddress1());
				cstmt.setString(fieldCounter++,employer.getAddress2());
				cstmt.setString(fieldCounter++,employer.getCity());
				cstmt.setString(fieldCounter++,employer.getState());
				cstmt.setString(fieldCounter++,employer.getCountry());
				cstmt.setString(fieldCounter++,employer.getZipCode());
				cstmt.setString(fieldCounter++,employer.getPhone());
				cstmt.setString(fieldCounter++,employer.getFax());
				cstmt.setString(fieldCounter++,employer.getWebsite());
				cstmt.setString(fieldCounter++,employer.getTaxId());
				cstmt.setString(fieldCounter++,employer.getEntityType());
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(employer.getEstablishmentDate(), OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setString(fieldCounter++,employer.getSicCode());				
				cstmt.setString(fieldCounter++,employer.getOPSRecordAuditInfo().getCreatedBy());
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_UPDATE_EMPLOYER());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_UPDATE_EMPLOYER());
		}
		LOG_DAO.info("Exit updateEmployer:DAO");
		return mpResult;

	}
	
	@Override
	public Map searchParameters(final String name,final String type,final String activeFrom, final String activeTo)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside searchParameters:DAO");
		LOG_DAO.info("Inside searchParameters:DAO, name="+name);
		LOG_DAO.info("Inside searchParameters:DAO, type="+type);
		LOG_DAO.info("Inside searchParameters:DAO, activeFrom="+activeFrom);
		LOG_DAO.info("Inside searchParameters:DAO, activeTo="+activeTo);
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlOutParameter("p_parameters", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("parameterId", rs.getString("PARAMETER_ID"));
						     hm.put("parameterType", rs.getString("PARAMETER_TYPE"));
						     hm.put("parameterKey", rs.getString("PARAMETER_KEY"));
						     hm.put("parameterName", rs.getString("PARAMETER_NAME"));
						     hm.put("parameterValue", rs.getString("PARAMETER_VALUE"));
						     hm.put("activeFrom", rs.getString("START_DATE_ACTIVE"));
						     hm.put("activeTo", rs.getString("END_DATE_ACTIVE"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_PARAMETERS(?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++,name);
				cstmt.setString(fieldCounter++,type);				
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchParameters:DAO");
		return mpResult;

	} 
	
	@Override
	public Map updateParameters(final String id,final String name,final String type,final String value,final String key,final String activeFrom, final String activeTo, final String userId, final String sessionId, final String operationFlag)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside updateParameters:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		LOG_DAO.info("Inside id="+id);
		LOG_DAO.info("Inside name="+name);
		LOG_DAO.info("Inside type="+type);
		LOG_DAO.info("Inside value="+value);
		LOG_DAO.info("Inside key="+key);
		LOG_DAO.info("Inside activeFrom="+activeFrom);
		LOG_DAO.info("Inside activeTo="+activeTo);
		LOG_DAO.info("Inside userId="+userId);
		LOG_DAO.info("Inside sessionId="+sessionId);
		LOG_DAO.info("Inside operationFlag="+operationFlag);
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_id", Types.NUMERIC));
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_value", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_key", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_session", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_operation_flag", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		LOG_DAO.info("Inside Parameter set");
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_UPDATE_PARAMETERS(?,?,?,?,?,?,?,?,?,?,?,?)");
				LOG_DAO.info("Inside Parameter set 0");
				cstmt.setString(fieldCounter++, (id ==null || (id != null && id.equals(""))) ? null : id);
				cstmt.setString(fieldCounter++,name);
				LOG_DAO.info("Inside Parameter set 1");
				cstmt.setString(fieldCounter++,type);
				cstmt.setString(fieldCounter++,value);	
				LOG_DAO.info("Inside Parameter set 2");
				cstmt.setString(fieldCounter++,key);	
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				LOG_DAO.info("Inside Parameter set 3");
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				LOG_DAO.info("Inside Parameter set 4");
				cstmt.setString(fieldCounter++,userId);
				cstmt.setString(fieldCounter++,sessionId);
				cstmt.setString(fieldCounter++, operationFlag);
				LOG_DAO.info("Inside Parameter set 5");
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				LOG_DAO.info("Inside Parameter set 6");
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchParameters:DAO");
		return mpResult;

	} 	
	
	@Override
	public Map searchMessages(final String name,final String lang,final String activeFrom, final String activeTo)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside searchMessages:DAO");
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_lang", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlOutParameter("p_messages", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("MESSAGE_ID", rs.getString("MESSAGE_ID"));
						     hm.put("MESSAGE_LANG", rs.getString("MESSAGE_LANG"));
						     hm.put("MESSAGE_KEY", rs.getString("MESSAGE_KEY"));
						     hm.put("MESSAGE_NAME", rs.getString("MESSAGE_NAME"));
						     hm.put("PARAMETER_VALUE", rs.getString("PARAMETER_VALUE"));
						     hm.put("START_DATE_ACTIVE", rs.getString("START_DATE_ACTIVE"));
						     hm.put("END_DATE_ACTIVE", rs.getString("END_DATE_ACTIVE"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_MESSAGES(?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++,name);
				cstmt.setString(fieldCounter++,lang);				
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchParameters:DAO");
		return mpResult;

	} 
	
	@Override
	public Map updateMessages(final String id,final String name,final String lang,final String key,final String value,final String activeFrom, final String activeTo, final String userId, final String sessionId, final String operationFlag)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside updateMessages:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		LOG_DAO.info("Inside id="+id);
		LOG_DAO.info("Inside name="+name);
		LOG_DAO.info("Inside value="+value);
		LOG_DAO.info("Inside key="+key);
		LOG_DAO.info("Inside activeFrom="+activeFrom);
		LOG_DAO.info("Inside activeTo="+activeTo);
		LOG_DAO.info("Inside userId="+userId);
		LOG_DAO.info("Inside sessionId="+sessionId);
		LOG_DAO.info("Inside operationFlag="+operationFlag);
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_id", Types.NUMERIC));
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_lang", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_key", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_value", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_session", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_operation_flag", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		LOG_DAO.info("Inside Parameter set");
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_UPDATE_MESSAGES(?,?,?,?,?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, (id ==null || (id != null && id.equals(""))) ? null : id);
				cstmt.setString(fieldCounter++,name);
				cstmt.setString(fieldCounter++,lang);
				cstmt.setString(fieldCounter++,key);
				cstmt.setString(fieldCounter++,value);	
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setString(fieldCounter++,userId);
				cstmt.setString(fieldCounter++,sessionId);
				cstmt.setString(fieldCounter++, operationFlag);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit updateMessages:DAO");
		return mpResult;

	} 	
	
	@Override
	public Map searchLookups(final String name,final String type,final String activeFrom, final String activeTo)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside searchLookups:DAO");
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlOutParameter("p_lookups", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("LOOKUP_ID", rs.getString("LOOKUP_ID"));
						     hm.put("LOOKUP_TYPE", rs.getString("LOOKUP_TYPE"));
						     hm.put("LOOKUP_VALUE_NAME", rs.getString("LOOKUP_VALUE_NAME"));
						     hm.put("LOOKUP_VALUE_KEY", rs.getString("LOOKUP_VALUE_KEY"));
						     hm.put("LOOKUP_VALUE_DESCRIPTION", rs.getString("LOOKUP_VALUE_DESCRIPTION"));
						     hm.put("ASSOCIATED_RULE", rs.getString("ASSOCIATED_RULE"));
						     hm.put("START_DATE_ACTIVE", rs.getString("START_DATE_ACTIVE"));
						     hm.put("END_DATE_ACTIVE", rs.getString("END_DATE_ACTIVE"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_LOOKUPS(?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++,name);
				cstmt.setString(fieldCounter++,type);				
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchLookups:DAO");
		return mpResult;

	} 
	
	@Override
	public Map updateLookups(final String id,final String type,final String name,final String key,final String value,final String rule,final String activeFrom, final String activeTo, final String userId, final String sessionId, final String operationFlag)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside updateLookups:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_id", Types.NUMERIC));
		lstParams.add(new SqlParameter("p_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_key", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_value", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_rule", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_session", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_operation_flag", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		LOG_DAO.info("Inside Parameter set");
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_UPDATE_LOOKUPS(?,?,?,?,?,?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, (id ==null || (id != null && id.equals(""))) ? null : id);
				cstmt.setString(fieldCounter++,type);
				cstmt.setString(fieldCounter++,name);
				cstmt.setString(fieldCounter++,key);
				cstmt.setString(fieldCounter++,value);
				cstmt.setString(fieldCounter++,rule);	
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setString(fieldCounter++,userId);
				cstmt.setString(fieldCounter++,sessionId);
				cstmt.setString(fieldCounter++, operationFlag);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit updateLookups:DAO");
		return mpResult;

	} 	
	
	@Override
	public Map searchRules(final String name,final String type,final String group,final String datatype,final String activeFrom, final String activeTo)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside searchRules:DAO");
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_group", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_datatype", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlOutParameter("p_rules", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("RULE_ID", rs.getString("RULE_ID"));
						     hm.put("RULE_GROUP", rs.getString("RULE_GROUP"));
						     hm.put("RULE_NAME", rs.getString("RULE_NAME"));
						     hm.put("RULE_DESCRIPTION", rs.getString("RULE_DESCRIPTION"));
						     hm.put("RULE_TYPE", rs.getString("RULE_TYPE"));
						     hm.put("RULE_DATATYPE", rs.getString("RULE_DATATYPE"));
						     hm.put("CALC_TYPE", rs.getString("CALC_TYPE"));
						     hm.put("RULE_FORMULA", rs.getString("RULE_FORMULA"));
						     hm.put("RULE_PARAMS", rs.getString("RULE_PARAMS"));
						     hm.put("RULE_FILTER", rs.getString("RULE_FILTER"));
						     hm.put("RULE_RATE", rs.getString("RULE_RATE"));
						     hm.put("RULE_LEVEL", rs.getString("RULE_LEVEL"));
						     hm.put("VALUE_FORMAT", rs.getString("VALUE_FORMAT"));
						     hm.put("IS_REQUIRED", rs.getString("IS_REQUIRED"));
						     hm.put("START_DATE_ACTIVE", rs.getString("START_DATE_ACTIVE"));
						     hm.put("END_DATE_ACTIVE", rs.getString("END_DATE_ACTIVE"));
						     
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_RULES(?,?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++,name);
				cstmt.setString(fieldCounter++,type);
				cstmt.setString(fieldCounter++,group);	
				cstmt.setString(fieldCounter++,datatype);	
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchRules:DAO");
		return mpResult;

	} 
	
	@Override
	public Map updateRules(final String id,final String ruleGroup,final String ruleName,final String description,final String ruleType,final String dataType,final String calcType,final String formula,final String parameters,final String filter,final String rate,final String level,final String valueFormat,final String required,final String activeFrom, final String activeTo, final String userId, final String sessionId, final String operationFlag)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside updateRules:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_id", Types.NUMERIC));
		lstParams.add(new SqlParameter("p_group", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_description", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_ruleType", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_dataType", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_calcType", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_formula", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_parameters", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_filter", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_rate", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_level", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_valueFormat", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_required", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_session", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_operation_flag", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_UPDATE_RULES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, (id ==null || (id != null && id.equals(""))) ? null : id);
				cstmt.setString(fieldCounter++,ruleGroup);
				cstmt.setString(fieldCounter++,ruleName);
				cstmt.setString(fieldCounter++,description);
				cstmt.setString(fieldCounter++,ruleType);
				cstmt.setString(fieldCounter++,dataType);
				cstmt.setString(fieldCounter++,calcType);
				cstmt.setString(fieldCounter++,formula);
				cstmt.setString(fieldCounter++,parameters);
				cstmt.setString(fieldCounter++,filter);
				cstmt.setString(fieldCounter++,rate);
				cstmt.setString(fieldCounter++,level);
				cstmt.setString(fieldCounter++,valueFormat);
				cstmt.setString(fieldCounter++,required);
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++,OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setString(fieldCounter++,userId);
				cstmt.setString(fieldCounter++,sessionId);
				cstmt.setString(fieldCounter++, operationFlag);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit updateRules:DAO");
		return mpResult;

	}
	
	@Override
	public Map searchRuleBinds(final String ruleName,final String bindVariable,final String bindType,final String ruleLevel) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside searchRuleRates:DAO");
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_bindVar", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_bindType", Types.DATE));
		lstParams.add(new SqlParameter("p_ruleLevel", Types.DATE));
		lstParams.add(new SqlOutParameter("p_binds", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("RULEBIND_ID", rs.getString("RULEBIND_ID"));
						     hm.put("RULE_NAME", rs.getString("RULE_NAME"));
						     hm.put("BIND_VAR", rs.getString("BIND_VAR"));
						     hm.put("BIND_VALUE", rs.getString("BIND_VALUE"));
						     hm.put("RULE_LEVEL", rs.getString("RULE_LEVEL"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_RULE_BINDS(?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++,ruleName);
				cstmt.setString(fieldCounter++,bindVariable);				
				cstmt.setString(fieldCounter++, bindType);
				cstmt.setString(fieldCounter++, ruleLevel);
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchRuleRates:DAO");
		return mpResult;		
	}
	
	@Override
	public Map searchRuleRates(final String ruleName,final String taxAuthority,final String taxType,final String rateType) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside searchRuleRates:DAO");
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_taxAuth", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_taxType", Types.DATE));
		lstParams.add(new SqlParameter("p_rateType", Types.DATE));
		lstParams.add(new SqlOutParameter("p_rates", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("RATE_ID", rs.getString("RATE_ID"));
						     hm.put("RANGE_RULE_NAME", rs.getString("RANGE_RULE_NAME"));
						     hm.put("TAX_AUTHORITY", rs.getString("TAX_AUTHORITY"));
						     hm.put("TAX_TYPE", rs.getString("TAX_TYPE"));
						     hm.put("RATE_TYPE", rs.getString("RATE_TYPE"));
						     hm.put("PARENT_RATE_ID", rs.getString("PARENT_RATE_ID"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_RATES(?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++,ruleName);
				cstmt.setString(fieldCounter++,taxAuthority);				
				cstmt.setString(fieldCounter++, taxType);
				cstmt.setString(fieldCounter++, rateType);
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchRuleRates:DAO");
		return mpResult;

	} 	
	
	public Map updateRuleRates(final String rateId,final String ruleName,final String taxAuthority,final String taxType,final String rateType,final String parentRateId,final String userId, final String sessionId, final String operationFlag)throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside updateRuleRates:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_id", Types.NUMERIC));
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_taxAuth", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_tax", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_parent", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_session", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_operation_flag", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_UPDATE_RATES(?,?,?,?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, (rateId ==null || (rateId != null && rateId.equals(""))) ? null : rateId);
				cstmt.setString(fieldCounter++,ruleName);
				cstmt.setString(fieldCounter++,taxAuthority);
				cstmt.setString(fieldCounter++,taxType);
				cstmt.setString(fieldCounter++,rateType);
				cstmt.setString(fieldCounter++,parentRateId);
				cstmt.setString(fieldCounter++,userId);
				cstmt.setString(fieldCounter++,sessionId);
				cstmt.setString(fieldCounter++, operationFlag);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit updateRuleRates:DAO");
		return mpResult;
		
	}
	
	@Override
	public Map searchUserSessions(final String userId,final String sessionStartTime,final String sessionEndTime) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside searchUserSessions:DAO");
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_user", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_session_start_time", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_session_end_time", Types.DATE));
		lstParams.add(new SqlOutParameter("p_user_sessions", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("USER_SESSION_ID", rs.getString("USER_SESSION_ID"));
						     hm.put("USER_ID", rs.getString("USER_ID"));
						     hm.put("LOGIN_TIME", rs.getString("LOGIN_TIME"));
						     hm.put("LOGOUT_TIME", rs.getString("LOGOUT_TIME"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_USER_SESSIONS(?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++,userId);
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(sessionStartTime, OPSConstant.getops_DB_DATE_FORMAT()));				
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(sessionEndTime, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchUserSessions:DAO");
		return mpResult;

	}	
	
	
	@Override
	public Map searchUsers(final String userName,final String status) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside searchUsers:DAO");
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_user_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_status", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("p_users", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("USER_ID", rs.getString("USER_ID"));
						     hm.put("USER_NAME", rs.getString("USER_NAME"));
						     hm.put("USER_STATUS", rs.getString("USER_STATUS"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("p_roles", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("USER_ID", rs.getString("USER_ID"));
						     hm.put("ROLE_ID", rs.getString("ROLE_ID"));
						     hm.put("START_DATE_ACTIVE", rs.getString("START_DATE_ACTIVE"));
						     hm.put("END_DATE_ACTIVE", rs.getString("END_DATE_ACTIVE"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_USERS(?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++,userName);
				cstmt.setString(fieldCounter++, status);
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchUsers:DAO");
		return mpResult;

	}	
	
	public Map updateUsers(final String id,final String name, final String status,final String password,final String userId, final String sessionId, final String operationFlag) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside updateUsers:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_id", Types.NUMERIC));
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_status", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_password", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_session", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_operation_flag", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("po_user_id", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_UPDATE_USERS(?,?,?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, (id ==null || (id != null && id.equals(""))) ? null : id);
				cstmt.setString(fieldCounter++, name);
				cstmt.setString(fieldCounter++, status);
				cstmt.setString(fieldCounter++, password);
				cstmt.setString(fieldCounter++, userId);
				cstmt.setString(fieldCounter++, sessionId);
				cstmt.setString(fieldCounter++, operationFlag);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit updateUsers:DAO");
		return mpResult;		
	}
	
	public Map updateRoles(final String uId, final String roleId,final String activeFrom,final String activeTo,final String userId, final String sessionId) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside updateUsers:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_u_id", Types.NUMERIC));
		lstParams.add(new SqlParameter("p_role_id", Types.NUMERIC));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_session", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_UPDATE_USER_ROLES(?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, (uId ==null || (uId != null && uId.equals(""))) ? null : uId);
				cstmt.setString(fieldCounter++, (roleId ==null || (roleId != null && roleId.equals(""))) ? null : roleId);
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setString(fieldCounter++, userId);
				cstmt.setString(fieldCounter++, sessionId);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit updateUsers:DAO");
		return mpResult;		
	}	
	
	
	@Override
	public Map searchUserActions(final String action,final String user,final String objectType,final String actionFromDate,final String actionToDate) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside searchUserActions:DAO");
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_action", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_object_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_action_from", Types.DATE));
		lstParams.add(new SqlParameter("p_action_to", Types.DATE));
		lstParams.add(new SqlOutParameter("p_user_actions", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("USER_ACTION_ID", rs.getString("USER_ACTION_ID"));
						     hm.put("USER_SESSION_ID", rs.getString("USER_SESSION_ID"));
						     hm.put("ACTION", rs.getString("ACTION"));
						     hm.put("OBJECT_TYPE", rs.getString("OBJECT_TYPE"));
						     hm.put("OBJECT_ID", rs.getString("OBJECT_ID"));
						     hm.put("ACTION_DATE", rs.getString("ACTION_DATE"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_USER_ACTIONS(?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, action);
				cstmt.setString(fieldCounter++, user);				
				cstmt.setString(fieldCounter++, objectType);
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(actionFromDate, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(actionToDate, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchUserActions:DAO");
		return mpResult;

	}	

	@Override
	public Map searchTaxAuthorities(final String name,final String taxType,final String rateType, final String ruleGroup,final String activeFrom,final String activeTo) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside searchTaxAuthorities:DAO");
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_tax_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_rate_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_rule_group", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlOutParameter("p_tax_authority", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("TAXAUTHORITY_ID", rs.getString("TAXAUTHORITY_ID"));
						     hm.put("TAX_AUTHORITY", rs.getString("TAX_AUTHORITY"));
						     hm.put("TAX_TYPE", rs.getString("TAX_TYPE"));
						     hm.put("RATE_TYPE", rs.getString("RATE_TYPE"));
						     hm.put("ADDRESS1", rs.getString("ADDRESS1"));
						     hm.put("ADDRESS2", rs.getString("ADDRESS2"));
						     hm.put("CITY", rs.getString("CITY"));
						     hm.put("STATE", rs.getString("STATE"));
						     hm.put("COUNTRY", rs.getString("COUNTRY"));
						     hm.put("ZIPCODE", rs.getString("ZIPCODE"));
						     hm.put("RULE_GROUP", rs.getString("RULE_GROUP"));
						     hm.put("START_DATE_ACTIVE", rs.getString("START_DATE_ACTIVE"));
						     hm.put("END_DATE_ACTIVE", rs.getString("END_DATE_ACTIVE"));
						     hm.put("createdBy", rs.getString("CREATED_BY"));
						     hm.put("createdDate", rs.getString("CREATED_DATE"));
						     hm.put("createdSession", rs.getString("CREATED_SESSION_ID"));
						     hm.put("updatedBy", rs.getString("UPDATED_BY"));
						     hm.put("updatedDate", rs.getString("UPDATED_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATED_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETED_BY"));
						     hm.put("deletedDate", rs.getString("DELETED_DATE"));
						     hm.put("deletedSession", rs.getString("DELETED_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_TAX_AUTHORITY(?,?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, name);
				cstmt.setString(fieldCounter++, taxType);				
				cstmt.setString(fieldCounter++, rateType);
				cstmt.setString(fieldCounter++, ruleGroup);
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit searchTaxAuthorities:DAO");
		return mpResult;

	}	
	
	public Map updateTaxAuthorities(final String id,final String name,final String taxType,final String rateType,final String address1,final String address2,final String city,final String state,final String country,final String zip, final String ruleGroup,final String activeFrom,final String activeTo, final String userId, final String sessionId, final String operationFlag) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside updateTaxAuthorities:DAO");
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_id", Types.NUMERIC));
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_tax_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_rate_type", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_address1", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_address2", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_city", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_state", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_country", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_zip_code", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_rule_group", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlParameter("p_user_id", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_user_session", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_operation_flag", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_UPDATE_TAX_AUTHORITY(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, (id ==null || (id != null && id.equals(""))) ? null : id);
				cstmt.setString(fieldCounter++, name);
				cstmt.setString(fieldCounter++, taxType);
				cstmt.setString(fieldCounter++, rateType);
				cstmt.setString(fieldCounter++, address1);
				cstmt.setString(fieldCounter++, address2);
				cstmt.setString(fieldCounter++, city);
				cstmt.setString(fieldCounter++, state);
				cstmt.setString(fieldCounter++, country);
				cstmt.setString(fieldCounter++, zip);
				cstmt.setString(fieldCounter++, ruleGroup);
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setString(fieldCounter++, userId);
				cstmt.setString(fieldCounter++, sessionId);
				cstmt.setString(fieldCounter++, operationFlag);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit updateTaxAuthorities:DAO");
		return mpResult;		
	}
	
	@Override
	public Map retrieveRoles(final String name,final String activeFrom,final String activeTo) throws OPSException {
		Map mpResult = null;
		LOG_DAO.info("Inside retrieveRoles:DAO");
		
		if(jdbc == null) {
			throw ExceptionHandler.generateDBConnectionOPSException();
		}
		
		try{
		// PARAMETER SETTING
		List<SqlParameter> lstParams = new ArrayList<SqlParameter>();
		lstParams.add(new SqlParameter("p_name", Types.VARCHAR));
		lstParams.add(new SqlParameter("p_active_from", Types.DATE));
		lstParams.add(new SqlParameter("p_active_to", Types.DATE));
		lstParams.add(new SqlOutParameter("p_roles", OracleTypes.CURSOR,
				new RowMapper<Map<String, String>>()
				{
					public Map<String, String> mapRow(ResultSet rs, int arg)throws SQLException
					   {
						     Map<String, String> hm = new HashMap<String, String>();
						     hm.put("ROLE_ID", rs.getString("ROLE_ID"));
						     hm.put("ROLE_NAME", rs.getString("ROLE_NAME"));
						     hm.put("START_DATE_ACTIVE", rs.getString("START_DATE_ACTIVE"));
						     hm.put("END_DATE_ACTIVE", rs.getString("END_DATE_ACTIVE"));
						     hm.put("createdBy", rs.getString("CREATE_BY"));
						     hm.put("createdDate", rs.getString("CREATE_DATE"));
						     hm.put("createdSession", rs.getString("CREATE_SESSION"));
						     hm.put("updatedBy", rs.getString("UPDATE_BY"));
						     hm.put("updatedDate", rs.getString("UPDATE_DATE"));
						     hm.put("updatedSession", rs.getString("UPDATE_SESSION"));
						     hm.put("deletedBy", rs.getString("DELETE_BY"));
						     hm.put("deletedDate", rs.getString("DELETE_DATE"));
						     hm.put("deletedSession", rs.getString("DELETE_SESSION"));
						     return hm;
					    }
				}
		        ));
		lstParams.add(new SqlOutParameter("pov_error_code", Types.VARCHAR));
		lstParams.add(new SqlOutParameter("pov_error_msg", Types.VARCHAR));
		//PROCEDURE CALL
		  mpResult = jdbc.call(new CallableStatementCreator(){

			public CallableStatement createCallableStatement(Connection con) throws SQLException 
			{
				int fieldCounter = 1;
				CallableStatement cstmt =  con.prepareCall("CALL SP_SEARCH_ROLES(?,?,?,?,?,?)");
				cstmt.setString(fieldCounter++, name);
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(activeFrom, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.setDate(fieldCounter++, OPSHelper.createSQLDate(activeTo, OPSConstant.getops_DB_DATE_FORMAT()));
				cstmt.registerOutParameter(fieldCounter++, OracleTypes.CURSOR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				cstmt.registerOutParameter(fieldCounter++, Types.VARCHAR);
				return cstmt;
			}
			},lstParams);
      }catch (DataAccessException ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_DATAACCESS_ERROR_LOGIN());
		}catch(Exception ex) {
			throw ExceptionHandler.generateOPSException(ex, OPSConstant.geterror_CODE_WAS_ERROR_LOGIN());
		}
		LOG_DAO.info("Exit retrieveRoles:DAO");
		return mpResult;

	}	
}
